<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

/***********************************************
 * @package		sqlite3
 * @subpackage	Modules
 * @category	Database
 * @author		Dennis T. Kaplan (TaMeR)
 * @copyright	Copyright (c) 2007, Orces, Inc.
 * @license		http://www.codeignitor.com/user_guide/license.html
 * @version		1.0
 * @link			http://www.pzzazz.com/
 ************************************************/

class sql3 {

	var $db_debug = TRUE;

	function sql3()
	{
		log_message('debug', 'sql3: Model initialized');
	}
	
	
	/**
	 * Non-persistent database connection
	 *
	 * @access	private called by the base class
	 * @return	resource
	 */	
	function db()
	{
		if ( ! $db = @sqlite3_open(APPPATH.'TaMeR.kexi'))
		{
			log_message('error', sqlite3_error($db));
			
			if ($this->db_debug)
			{
				show_error(sqlite3_error($db));
			}
			
			return FALSE;
		}

		return $db;
	}

	function getCTZ()
	{
		$db = $this->db();
		$sql = "SELECT tz.tz, tz.tz_linux, country.country 
					FROM tz, country 
					WHERE country.ISO3166 = tz.ISO3166";

		if ( $dbRes = sqlite3_query($db, $sql)) 
		{
			$return = '<table>';
			while($b = sqlite3_fetch_array($dbRes))
			{
				$b = (object) $b;
				$return .= "<tr><td>$b->tz</td><td>$b->tz_linux</td><td>$b->country</td></tr>";
			}
			$return .= '</table>';
			sqlite3_query_close($dbRes);
			sqlite3_close($db);
			return $return;
		}
	}

	
	function dbGet($cat,$name,$for = FALSE,$value = FALSE)
	{
		$db = $this->db();
		$data = array(
			'cat' 	=> "'$cat'",
			'name' 	=> "'$name'"
			);
		
		if($for){
			$data['for'] = $for;
		}
		if($value){
			$data['value'] = $value;
		}
		$where  = '';
		foreach($data as $v => $k)
		{
			$where  .= "`$v` = $k AND";
		}
		$where  = $this->RLComma($where);

		$sql = "SELECT * FROM `data` WHERE $where";
		if ( $dbRes = sqlite3_query($db, $sql))
		{
			while($query = sqlite3_fetch_array($dbRes))
			{
				$result[] = $query;
			}
		}
		else
		{
			$result = FALSE;
		}
		sqlite3_query_close($dbRes);
		sqlite3_close($db);
		return  $result;
	}

	function dbPut($cat,$name,$for,$value,$ext = FALSE)
	{
		$value = sqlite_escape_string($value);
		$select = $this->dbGet($cat,$name,$for,$value);
		
		if ($select !== FALSE) // Update
		{
			if($ext)
			{
				$set = "`ext` = '$ext'";
			}
			else
			{
				$set = "`value`= '$value'";
			}
			$id = $select[0]->id;
			$sql = "UPDATE `data` SET $set WHERE `id` = $id";
			$query = $this->db->query($sql); 
		}
		else // Insert
		{
			if($ext)
			{
				$sql = "INSERT INTO `data` (`cat`, `name`, `for`, `value`,`ext`) 
						VALUES ('$cat', '$name', '$for', '$value', '$ext')";
			}
			else
			{
				$sql = "INSERT INTO `data` (`cat`, `name`, `for`, `value`) 
						VALUES ('$cat', $name, '$for', $value)";
			}
			$query = $this->db->query($sql);
		}
	}


	function selectCategory()
	{
		$db = $this->db();
		$sql = "SELECT DISTINCT `name` FROM `data` WHERE `cat` = 'Select'";
		if ( $dbRes = sqlite3_query($db, $sql))
		{
			$result = "<select name=\"Category\">\n";
			$result .= "\t<option value=\"\">-Select-</option>\n";
			while($query = sqlite3_fetch_array($dbRes))
			{
				$name = $query['name'];
				$result .= "\t<option value=\"$name\">$name</option>\n";
			}
			$result .= "</select>\n";
			$result .= "<input type=\"submit\" name=\"submitcat\" value=\"Select\">";
		}
		else
		{
			$result = FALSE;
		}
		sqlite3_query_close($dbRes);
		sqlite3_close($db);
		return  $result;
	}


	
	function RLComma($string)
	{
		//here we remove the last comma.
		$string = trim($string);
		$last = $string{strlen($string)-1};
		if (!strcmp($last,",")){
			$string = rtrim($string, ',');
			$string = trim($string);
		}
		log_message('debug', 'SQL3: string: ' . $string);
		$last = $string{strlen($string)-1};
		log_message('debug', 'SQL3: last' . $last);
		if (!strcmp($last,"D")){
			$string = rtrim($string, 'AND');
			log_message('debug', 'SQL3: string' . $string);
			$string = trim($string);
		}

		return $string;
	}


	
	function sq3_num_rows($dbRes)
	{
		// How to use it:
		// echo "Number of rows: " . sqlite3_num_rows($dbRes). "<br />";
		$n = 0;
		while($a = sqlite3_fetch_array($dbRes))
		{
			$n = $n + 1;
		}
		return $n;
	}

}
?>
